﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace DatabaseCompare.ServiceImpl.Reader
{

    public class Sql2005DatabaseInfoReader : AbstractDatabaseInfoReader
    {


        #region 读取表/字段信息的SQL语句


        /// <summary>
        /// 读取表信息的 SQL
        /// </summary>
        private const string READ_TABLE_INFO_SQL = @"select
  a.name AS TableName,
  isnull(g.[value],'-') AS Comment
from
  sys.tables a left join sys.extended_properties g
  on (a.object_id = g.major_id AND g.minor_id = 0)
order by 
  a.name
";

        /// <summary>
        /// 读取表的列信息的 SQL
        /// </summary>
        private const string READ_TABLE_COLUMN_INFO_SQL = @"SELECT
   tab.name AS TableName,
   col.name AS ColumnName,
   col.column_id  AS  ColumnId,
   typ.name as DataType,
   col.max_length AS MaxLength,
   col.precision AS Precision,
   col.Scale AS Scale,
   col.is_nullable  AS IsNullAble,
   col.is_identity  AS IsIDEntity,
   case when exists 
      ( SELECT 1 
        FROM 
          sys.indexes idx 
            join sys.index_columns idxCol 
            on (idx.object_id = idxCol.object_id)
         WHERE
            idx.object_id = col.object_id
            AND idxCol.index_column_id = col.column_id
            AND idx.is_primary_key = 1
       ) THEN 1 ELSE 0 END  AS IsPrimaryKey,
  isnull(prop.[value],'-') AS Comment
FROM
  sys.tables tab left join
  (sys.columns col 
    left join sys.types typ 
      on (col.system_type_id = typ.system_type_id)
    left join sys.extended_properties prop
      on (col.object_id = prop.major_id AND prop.minor_id = col.column_id)
  ) on (tab.object_id = col.object_id)
";



        /// <summary>
        /// 读取视图信息的 SQL
        /// </summary>
        private const string READ_VIEW_INFO_SQL = @"select
  a.name AS ViewName,
  '-' AS Comment,
  c.text AS CreateViewSQL
from
  sys.views a 
  LEFT OUTER JOIN
       dbo.syscomments c ON a.object_id = c.id
order by 
  a.name";


        /// <summary>
        /// 读取视图的列信息的 SQL
        /// </summary>
        private const string READ_VIEW_COLUMN_INFO_SQL = @"SELECT
   vie.name AS ViewName,
   col.name AS ColumnName,
   col.column_id  AS  ColumnId,
   typ.name as DataType,
   col.max_length AS MaxLength,
   col.precision AS Precision,
   col.Scale AS Scale,
   col.is_nullable  AS IsNullAble,
   col.is_identity  AS IsIDEntity,
   case when exists 
      ( SELECT 1 
        FROM 
          sys.indexes idx 
            join sys.index_columns idxCol 
            on (idx.object_id = idxCol.object_id)
         WHERE
            idx.object_id = col.object_id
            AND idxCol.index_column_id = col.column_id
            AND idx.is_primary_key = 1
       ) THEN 1 ELSE 0 END  AS IsPrimaryKey,
  isnull(prop.[value],'-') AS Comment
FROM
  sys.views vie left join
  (sys.columns col 
    left join sys.types typ 
      on (col.system_type_id = typ.system_type_id)
    left join sys.extended_properties prop
      on (col.object_id = prop.major_id AND prop.minor_id = col.column_id)
  ) on (vie.object_id = col.object_id)";




        /// <summary>
        /// 读取触发器信息的 SQL
        /// </summary>
        private const string READ_TRIGGER_INFO_SQL = @"select 
  tri.name  AS  TriggerName,
  '-' AS Comment,
  tri.is_disabled AS IsDisable,
  tri.is_instead_of_trigger AS IsInsteadOfTrigger,
  c.text AS CreateTriggerSQL
from 
  sys.triggers tri LEFT OUTER JOIN
     dbo.syscomments c ON tri.object_id = c.id";


        /// <summary>
        /// 读取存储过程信息的 SQL
        /// </summary>
        private const string READ_PROCEDURE_INFO_SQL = @"select 
  pro.name  AS  ProcedureName,
  '-' AS Comment,
  c.text AS CreateProcedureSQL
from 
  sys.procedures pro LEFT OUTER JOIN
     dbo.syscomments c ON pro.object_id = c.id
where
  c.colid = 1";


        /// <summary>
        /// 读取函数的 SQL
        /// </summary>
        private const string READ_FUNCTION_INFO_SQL = @"SELECT 
    o.name AS FunctionName, 
   '-' AS Comment,
    c.text AS CreateFunctionSQL
FROM 
  dbo.sysobjects o LEFT OUTER JOIN
     dbo.syscomments c ON o.id = c.id
WHERE 
  o.xtype IN ('IF', 'TF', 'FN') 
   AND (OBJECTPROPERTY(o.id, N'IsMSShipped') = 0)
   AND c.colid = 1
";


        #endregion




        /// <summary>
        /// 将数据库信息，读取到 DataSet 中.
        /// </summary>
        /// <returns></returns>
        public override DataSet ReadDatabaseInfoToDataSet()
        {
            // 建立数据库连接.
            SqlConnection conn = new SqlConnection(ConnectString);
            // 创建DataSet，用于存储数据.
            DataSet resultDataSet = new DataSet();

            // 创建一个适配器
            SqlDataAdapter tableAdapter = new SqlDataAdapter(READ_TABLE_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            tableAdapter.Fill(resultDataSet, "TableInfo");

            // 创建一个适配器
            SqlDataAdapter tableColumnAdapter = new SqlDataAdapter(READ_TABLE_COLUMN_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            tableColumnAdapter.Fill(resultDataSet, "TableColumnInfo");


            // 创建一个适配器
            SqlDataAdapter viewAdapter = new SqlDataAdapter(READ_VIEW_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            viewAdapter.Fill(resultDataSet, "ViewInfo");

            // 创建一个适配器
            SqlDataAdapter viewColumnAdapter = new SqlDataAdapter(READ_VIEW_COLUMN_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            viewColumnAdapter.Fill(resultDataSet, "ViewColumnInfo");


            // 创建一个适配器
            SqlDataAdapter triggerColumnAdapter = new SqlDataAdapter(READ_TRIGGER_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            triggerColumnAdapter.Fill(resultDataSet, "TriggerInfo");


            // 创建一个适配器
            SqlDataAdapter procedureColumnAdapter = new SqlDataAdapter(READ_PROCEDURE_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            procedureColumnAdapter.Fill(resultDataSet, "ProcedureInfo");


            // 创建一个适配器
            SqlDataAdapter functionColumnAdapter = new SqlDataAdapter(READ_FUNCTION_INFO_SQL, conn);
            // 执行查询，并将数据导入DataSet.
            functionColumnAdapter.Fill(resultDataSet, "FunctionInfo");


            // 关闭数据库连接.
            conn.Close();
            return resultDataSet;
        }
    }
}
